# --*--coding: utf-8 --*--
__project__ = 'pythonProject1'
__fileName = 'test1'
__author = 'admin'
__time = '2021/4/21 17:05'
import pymysql


def insert_table():
    try:
        # 创建connect连接
        db = pymysql.connect(host="49.233.39.160", user="user", passwd="leboAa!#$123", db="lebo16", port=3306)
        # 获取Cursor对象
        curso = db.cursor()
        # 茶树数据
        sql = """insert into students values
	        ('001','布丁1','女','北京','20','1班','2323'),
	        ('001','布丁2','女','北京','20','1班','2323'),
	        ('001','布丁3','女','北京','20','1班','2323')
	        """
        try:
            # 执行插入数据
            curso.execute(sql)
            # 提交之前的操作
            db.commit()
            print('插入成功')
        except Exception as result:
            print('插入失败')
            print(result)
    except Exception as t:
        print(t)


#insert_table()


def main():
    # 创建连接
    conn = pymysql.connect(host="49.233.39.160", user='user', passwd='leboAa!#$123', database='lebo', port=3306,
                           charset='utf8')
    # 获取Cursor对象
    cs1 = conn.cursor()
    # 更新数据
    updat = cs1.execute("update students set spname='12' where spname='布丁'")
    print("更新成功")

    delete = cs1.execute('delete from students where spname="天天"')
    conn.commit()  # 提交之前的操作，如果之前有那么一起提交
    # 关闭Cursor对象
    cs1.close()
    # 关闭连接
    conn.close()


# 创建数据表

def create_table():
    try:
        # 连接数据库
        #conn = pymysql.connect(host="49.233.39.160", user='user', passwd='leboAa!#$123', database='lebo16', port=3306,
                          # charset='utf8')
        conn = pymysql.connect(host="49.233.39.160", user="user", passwd="leboAa!#$123", db="lebo16", port=3306)
         # 打开游标
        cur = conn.cursor()
        delsql = cur.execute('drop table  if exists studengsbd')
        sql = """create table studengsbd(
        id int unsigned primary key auto_increment,
        userName varchar(100),
        userAge int unsigned,
        heghit decimal(5,2))"""
        try:
            cur.execute(sql)
            print('创建表studengsbd成功')
            conn.commit()
        except Exception as repr:
            print('创建studengsbd表失败')
            conn.rollback()
            print(repr)
        finally:
            conn.close()
            cur.close()

    except Exception  as  error:
        print('插入失败')
        print(error)

#插入数据
def add_studeng():
    try:
        #连接数据库
        con=pymysql.connect(host="49.233.39.160", user="user", passwd="leboAa!#$123", db="lebo16", port=3306)
         #打开游标
        cursor =con.cursor()
     #
        sql="""insert into studengsbd values(001,
        '布丁',
        18,
        160)
        """
        try:
            cursor.execute(sql)
            con.commit()
            print('插入数据成功')
        except Exception as error:
            print('插入失败%s'% error)
            con.rollback()
        finally:
            con.close()
    except Exception as  res:
        print(res)

def select():
    conn = pymysql.connect(host="49.233.39.160", user='user', passwd='leboAa!#$123', database='lebo16', port=3306,
                           charset='utf8')
    cursor1 = conn.cursor()
    sql = cursor1.execute('select * from studengsbd where userName like \"布丁%\"')
    print('打印收到影响行数%d' % sql)
    for i in range(sql):
        # 获取结果
        res = cursor1.fetchone()
        print(res)

#调用创建表的函数
#create_table()
#调用插入数据函数
add_studeng()
select()